package com.eam.service;

import com.yy.base.common.dao.Dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

@Service
public class SparePartsRecordService {
	@Autowired
	private Dao dao;
	
	//加载所有的设备备品配件领用信息
	public List<Map<String,Object>> getSparepartsList() {
		
		return dao.query("select to_char(jl.collar_time, 'yyyy-MM-dd hh24:mm:ss ') as rktime, jl.Eam_Spareparts_collar_id as JLID , wl.Eam_Spareparts_id as WLID ,"
				+ "  wl.code as wlcode ,wl.name as wlname , wl.model , wl.numbers as wlnumbers, jl.numbers as lynumbers, "
				+ " (case jl.company when '10' then '对' when '20' then '个'   when '30' then '根'  when '40' then '盒' when '50' then '卷' when '60' then  '块'  when '70' then '片' when '80' then '套' else ''  end) as company, "
				+ " gx.name as gxname ,sb.name as sbname , kw.value ,yh.name  as yhname ,jl.remarks ,"
				+ " (case jl.status when 'Audit' then '待审核' when 'passed' then '已通过'  when 'Notthrough' then '不通过' else '' end) as jlstatus , ORG.NAME AS ORGNAME "
				+ " from  Eam_Spareparts_Collar jl "
				+ " left join Eam_Spareparts wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id  "
				+ " left join M_Locator kw on jl.m_locator_id = kw.m_locator_id  "
				+ " left join ad_user yh on yh.ad_user_id = jl.ad_user_id  "
				+ " left join emes_processopr gx "
				+ " on gx.emes_processopr_id = jl.emes_processopr_id "
				+ " left join Eam_Facilities sb"
				+ " on jl.Eam_Facilities_id = sb.Eam_Facilities_id "
				+ "  LEFT JOIN AD_ORG ORG ON ORG.AD_ORG_ID = JL.AD_ORG_ID "
				+ " where   jl.type = 'Out' "
				+ " order by jl.status, wl.name ");
	}
	
	//加载设备备品配件物料的库存数量
		public List<Map<String,Object>> getSumMateriels(String wlcode) {
			return dao.query("select ((select sum(jl.numbers)   "
					+ "from Eam_Spareparts_Collar jl "
					+ " left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id "
					+ " where jl.type = 'added' and wl.code = '"+wlcode+"') -   "
					+ "(select NVL(sum(jl.numbers), 0)"
					+ "  from Eam_Spareparts_Collar jl "
					+ "  left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id "
					+ "  where jl.type = 'Out' and wl.code = '"+wlcode+"' and jl.status != 'Notthrough' )) as qty  from dual " );
					
		}
	
	/*//设备备品配件审核不通过领用数量返回库存
	@SuppressWarnings("unchecked")
	public boolean getUpdaeteSpareParts(int numbers , String ids){
		boolean result = false;
			
		String sql =  "UPDATE Eam_Spareparts SET Numbers = ?  WHERE Eam_Spareparts_id = ?";
	  		
		result = dao.executeSql(sql, numbers , ids) > 0;
			
		return result;
	}*/
		
		
	//设备备品配件出入库记录更改审核状态（通过的审核）
	@SuppressWarnings("unchecked")
	public boolean getUpdaeteSparePartsRecord(String status , String ids , int updateqty , int COLLARs){
		boolean result = false;
			
		String sql =  "UPDATE Eam_Spareparts_Collar SET Status = ? ,Numbers = ? , Stock_numbers = ?  WHERE Eam_Spareparts_Collar_id = ?";
	  		
		result = dao.executeSql(sql, status , COLLARs, updateqty, ids) > 0;
			
		return result;
	}
	
	//设备备品配件出入库记录更改审核状态(不通过的审核)
	@SuppressWarnings("unchecked")
	public boolean getUpdaeteSparePartsRecords(String status , String ids){
		boolean result = false;
				
		String sql =  "UPDATE Eam_Spareparts_Collar SET Status = ? WHERE Eam_Spareparts_Collar_id = ?";
		  		
		result = dao.executeSql(sql, status , ids) > 0;
				
		return result;
	}
	
	

}
